﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_F4MaterialKosei : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        public panel_F4MaterialKosei()
        {
            InitializeComponent();
            mySetting = new INISettings();
            myConnection = new SqlConnection(mySetting.SQLConnectionString);
            LoadData();
            this.F4MaterialKosei.DoubleClick += F4MaterialKosei_DoubleClick;
            this.F4MaterialKosei.PopupMenuShowing += F4MaterialKosei_PopupMenuShowing;
            this.f4MaterialKoseiContextMenu.ItemClicked += f4MaterialKoseiContextMenu_ItemClicked;
        }

        private void ImportExcel()
        {
            OleDbDataAdapter adp;
            DataTable excel_data = new DataTable();

            bool have_data = false;

            importFile.Filter = "Excel 2007-2013 (.xlsx)|*.xlsx";
            importFile.FilterIndex = 1;
            importFile.Multiselect = false;
            DialogResult userClickOK = importFile.ShowDialog();
            if (userClickOK == DialogResult.OK)
            {
                // Import new data from Excel
                OleDbConnection excelConnection = null;
                try
                {
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        importFile.FileName + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd = new OleDbCommand("Select [CompID], [PartNo], [Quantity], [Unit], [Group]"
                        + " from [Sheet1$]", excelConnection);
                    excelConnection.Open();
                    adp = new OleDbDataAdapter(cmd);
                    adp.Fill(excel_data);
                    have_data = true;
                    excelConnection.Close();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    have_data = false;
                    MessageBox.Show("Get data error: " + ex.Message);
                    //MessageBox.Show("Cannot import data from file. Please check your file");
                }
            }

            if (have_data)
            {
                string error_text = null;
                int i = 2;
                foreach (DataRow excel_row in excel_data.Rows)
                {
                    bool pass = true;
                    float quantity = 0;                    
                    if (string.IsNullOrEmpty(excel_row["CompID"].ToString().Trim()))
                    {
                        error_text += "Line " + i.ToString() + " error: CompID is null.\n\r";
                        pass = false;
                    }

                    if (pass)
                    {
                        if (!checkExistPart(excel_row["CompID"].ToString().Trim()))
                        {
                            error_text += "Line " + i.ToString() + " error: Invalid CompID.\n\r";
                            pass = false;
                        }
                    }

                    if (pass)
                    {
                        if (string.IsNullOrEmpty(excel_row["PartNo"].ToString().Trim()))
                        {
                            error_text += "Line " + i.ToString() + " error: PartNo is null.\n\r";
                            pass = false;
                        }
                    }

                    if (pass)
                    {
                        if (!checkExistPart(excel_row["PartNo"].ToString().Trim()))
                        {
                            error_text += "Line " + i.ToString() + " error: Invalid PartNo.\n\r";
                            pass = false;
                        }                        
                    }

                    if (pass)
                    {
                        if (checkExistKosei(excel_row["CompID"].ToString().Trim(), excel_row["PartNo"].ToString().Trim(), excel_row["Group"].ToString().Trim()))
                        {
                            error_text += "Line " + i.ToString() + " error: CompID and PartNo existed.\n\r";
                            pass = false;
                        }
                    }

                    if (pass)
                    {
                        if (float.TryParse(excel_row["Quantity"].ToString().Trim(), out quantity))
                        {
                            if (quantity <= 0)
                            {
                                error_text += "Line " + i.ToString() + " error: Quantity must be >= 0.\n\r";
                                pass = false;
                            }
                        }
                        else
                        {
                            error_text += "Line " + i.ToString() + " error: Invalid quantity.\n\r";
                            pass = false;
                        }
                    }

                    if (pass)
                    {
                        try
                        {
                            myConnection.Open();
                            SqlCommand sql_cmd = myConnection.CreateCommand();
                            sql_cmd.CommandText = "INSERT INTO [231_Kosei] (RobotNo, PartNo, Quantity, Unit, [Group])"
                                + " VALUES ('" + excel_row["CompID"].ToString().Trim() + "',"
                                + " '" + excel_row["PartNo"].ToString().Trim() + "', " + quantity + ","
                                + " '" + excel_row["Unit"].ToString().Trim() + "', '" + excel_row["Group"].ToString().Trim() + "')";
                            sql_cmd.ExecuteNonQuery();
                            sql_cmd.Dispose();
                            myConnection.Close();
                        }
                        catch
                        {
                            if (myConnection.State == ConnectionState.Open)
                            {
                                myConnection.Close();
                            }
                            error_text += "Line " + i.ToString() + " error: Cannot insert.\n\r";
                        }
                    }
                    i++;
                }

                if (string.IsNullOrEmpty(error_text))
                {
                    MessageBox.Show("Data import succeedfully.");
                }
                else
                {
                    MessageBox.Show(error_text);
                }
            }
        }

        /// <summary>
        /// Check exist CompID and PartNo
        /// </summary>
        private bool checkExistKosei(string comp, string part, string group)
        {
            bool ret = false;
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT COUNT(ID) FROM [231_Kosei]"
                    + " WHERE RobotNo='" + comp + "' AND PartNo='" + part + "' AND [Group]='" + group + "'";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    if (dataReader[0] != DBNull.Value)
                    {
                        if (!string.IsNullOrEmpty(dataReader[0].ToString().Trim()))
                        {
                            if (Int32.Parse(dataReader[0].ToString().Trim()) > 0)
                            {
                                ret = true;
                            }
                        }
                    }
                }
                dataReader.Close();
                sql_cmd.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot check Kosei for CompID: " + comp + " and PartNo: " + part);
                ret = true;
            }
            return ret;
        }

        /// <summary>
        /// Check exist PartNo
        /// </summary>
        private bool checkExistPart(string part)
        {
            bool ret = false;
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT COUNT(PartNo) FROM [222_Part]"
                    + " WHERE PartNo='" + part + "'";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    if (dataReader[0] != DBNull.Value)
                    {
                        if (!string.IsNullOrEmpty(dataReader[0].ToString().Trim()))
                        {
                            if (Int32.Parse(dataReader[0].ToString().Trim()) > 0)
                            {
                                ret = true;
                            }
                        }
                    }
                }
                dataReader.Close();
                sql_cmd.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot check Part: " + part);
                ret = true;
            }
            return ret;
        }

        private void editRecord()
        {
            DataRow row = F4MaterialKosei.GetFocusedDataRow();
            frm_Edit_F4MaterialKosei editForm = new frm_Edit_F4MaterialKosei();
            editForm.ID = row[0].ToString();
            if (editForm.LoadData())
            {                
                editForm.ShowDialog();
                LoadData();
            }
            else
            {
                MessageBox.Show("Cannot load data for selected record");
            }
        }

        void f4MaterialKoseiContextMenu_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.f4MaterialKoseiContextMenu.Hide();
            DataRow row = F4MaterialKosei.GetFocusedDataRow();
            switch (e.ClickedItem.Name)
            {                
                case "addSelection":
                    frm_Add_F4MaterialKosei export_Form = new frm_Add_F4MaterialKosei();
                    export_Form.ShowDialog();
                    LoadData();
                    break;
                case "editSelection":
                    editRecord();
                    break;
                case "deleteSelection":
                    deleteRecord();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void F4MaterialKosei_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.f4MaterialKoseiContextMenu.Show(MousePosition);
        }

        private void F4MaterialKosei_DoubleClick(object sender, EventArgs e)
        {
            editRecord();
        }
        
        //////////////////////////////////////////////////
        /// Load data from database to grid
        //////////////////////////////////////////////////
        private void LoadData()
        {            
            DataSet sourceDataSet = new DataSet();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySetting.SQLConnectionString;
            string sql = "SELECT * FROM [231_Kosei]";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridF4MaterialKosei.DataSource = sourceDataSet.Tables[0];
            }
            else
            {
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(int));
                newTable.Columns.Add("RobotNo", typeof(string));
                newTable.Columns.Add("PartNo", typeof(string));
                newTable.Columns.Add("Quantity", typeof(float));
                newTable.Columns.Add("Unit", typeof(string));
                newTable.Columns.Add("Group", typeof(string));
                gridF4MaterialKosei.DataSource = newTable;                
            }
            F4MaterialKosei.Columns[0].Caption = "ID";
            F4MaterialKosei.Columns[1].Caption = "Robot No.";
            F4MaterialKosei.Columns[2].Caption = "Part No.";
            F4MaterialKosei.Columns[3].Caption = "Quantity";            
            F4MaterialKosei.Columns[4].Caption = "Unit";
            F4MaterialKosei.Columns[5].Caption = "Group";
            F4MaterialKosei.Columns[0].Visible = false;
            F4MaterialKosei.BestFitColumns();
        }

        private void bottonAdd_Click(object sender, EventArgs e)
        {
            frm_Add_F4MaterialKosei export_Form = new frm_Add_F4MaterialKosei();
            export_Form.ShowDialog();
            LoadData();
        }

        private void buttonEdit_Click(object sender, EventArgs e)
        {
            editRecord();
        }

        private void deleteRecord()
        {
            int[] rows = F4MaterialKosei.GetSelectedRows();
            if (rows.Length <= 0)
            {
                MessageBox.Show("There is no selected record to delete");
            }
            else
            {
                if (MessageBox.Show("Are you really to delete selected records now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    string[] id_list = new string[rows.Length];
                    for (int i = 0; i < rows.Length; i++)
                    {
                        DataRow selected_row = F4MaterialKosei.GetDataRow(rows[i]);
                        id_list[i] = "'" + selected_row[0].ToString() + "'";
                    }
                    string id = string.Join(",", id_list);

                    try
                    {
                        myConnection.Open();
                        string sql = "DELETE [231_Kosei] WHERE ID IN (" + id + ")";
                        SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();
                        myConnection.Close();
                        MessageBox.Show("Material Kosei deleted success.");
                        LoadData();
                    }
                    catch //(Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        //MessageBox.Show(ex.Message);
                        MessageBox.Show("Cannot delete Material Kosei. Please try again.");
                    }
                }
            }
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteRecord();
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            ImportExcel();
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }
    }
}
